from src.main.dao.BaseDao import BaseDao

class StudentDao(BaseDao):
    def insert_student(self, name, age, grade, gender='', email='', phone='', address='', enrollment_date=''):
        query = "INSERT INTO student_info (name, age, grade, gender, email, phone, address, enrollment_date) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
        values = (name, age, grade, gender, email, phone, address, enrollment_date)
        return self.execute_sql(query, values)

    def update_student(self, student_id, name, age, grade, gender='', email='', phone='', address='', enrollment_date=''):
        query = "UPDATE student_info SET name = %s, age = %s, grade = %s, gender = %s, email = %s, phone = %s, address = %s, enrollment_date = %s WHERE id = %s"
        values = (name, age, grade, gender, email, phone, address, enrollment_date, student_id)
        return self.execute_sql(query, values)

    def delete_student(self, student_id):
        query = "DELETE FROM student_info WHERE id = %s"
        values = (student_id,)
        return self.execute_sql(query, values)

    def get_all_student_info(self):
        query = "SELECT * FROM student_info"
        return self.execute_query(query)

    def get_student_by_id(self, student_id):
        query = "SELECT * FROM student_info WHERE id = %s"
        values = (student_id,)
        return self.execute_query(query, values)

    def get_student_by_name(self, name):
        query = "SELECT * FROM student_info WHERE name = %s"
        values = (name,)
        return self.execute_query(query, values)

    def get_student_info_by_page(self, page_number, page_size,filter_gender,filter_keyword):
        offset = (page_number - 1) * page_size
        query = "SELECT * FROM student_info where 1=1"

        # 添加性别筛选条件
        if filter_gender:
            query += " AND gender = '{}'".format(filter_gender)

        # 添加关键词筛选条件
        if filter_keyword:
            query += " AND (name LIKE '%{}%' OR grade LIKE '%{}%')".format(filter_keyword, filter_keyword)

        # 添加分页条件
        query += " LIMIT {} OFFSET {}".format(page_size, (page_number - 1) * page_size)

        return self.execute_query(query)
